use master
go
	if EXISTS(select name FROM sys.databases WHERE name = 'Radio_Cabs')
		drop database [Radio_Cabs]
go
begin try
	create database [Radio_Cabs]
	ON
	(
	name=Radio_Cabs,
	filename='C:\Radio_Cabs.mdf',
	size=100MB,
	maxsize=10GB,
	filegrowth=10MB
	)

	LOG ON
	(
	name=Radio_Cabs_log,
	filename='C:\Radio_Cabs_log.ldf',
	size=100MB,
	maxsize=10GB,
	filegrowth=10MB
	)
end try
begin catch
	print N'Cannot create database'
end catch
go
begin try
	use [Radio_Cabs]

	create table [AccountsType]
	(
		[TypeId] int identity(0,1) constraint PK_AccountType primary key ,
		[TypeName] varchar(50) not null unique
	)
	print N'Create table AccountType Success'
	
	create table [PaymentRate]
	(
		[PaymentId] int identity(0,1) constraint PK_PaymentRate primary key,
		[PaymentName] varchar(30) not null,
		[PaymentPrice] float not null,
		[NoDate] int not null	--chua chuan. chua check trung dc
	)
	print N'Create table PaymentRate Success'
	
	create table [City]
	(
		[CityId] int identity(0,1) primary key,
		[CityName] varchar(50) not null unique
	)
	print N'Create table City Success'
	
	create table [Accounts]
	(
		[AccId] int identity(0,1) constraint PK_Accounts primary key,
		[Uid] varchar(50) not null unique,
		[Pwd] varchar(50) not null,
		[Gender] bit not null,
		[JoinDate] datetime not null,
		[StartDate] datetime,
		[EndDate] datetime,
		[Img] varchar(max),
		[TypeId] int constraint FK_AccountDetails_AccountType foreign key references AccountsType(TypeId),
		[Status] bit not null default 0
	)
	print N'Create table Accounts Success'

	create table [AccountDetails]
	(
		[AccId] int constraint PK_AccountDetails primary key constraint FK_AccountDetails_Account foreign key references Accounts(AccId),
		[ContactPerson] nvarchar(50) not null,
		[CityId] int not null constraint FK_AccountsDetails_City foreign key references City(CityId),
		[Mobile] varchar(50) not null,
		[Telephone] varchar(50) not null,
		[Address] varchar(max) not null,
		[Email] varchar(50) not null unique
	)
	print N'Create table AccountDetails Success'

	create table [PaymentDetails]
	(
		[AccId] int constraint FK_PaymentDetails_Accounts foreign key references Accounts(AccId),
		[PaymentId] int not null constraint FK_PaymentDetails_PaymentRate foreign key references PaymentRate(PaymentId),
		[PaymentDate] datetime default getdate(),
		[Amount] float not null,
		[Status] bit not null
		constraint PK_PaymentDetails primary key(AccId,PaymentId,PaymentDate)
	)
	print N'Create table PaymentDetails Success'

	create table [Company]
	(
		[CompanyId] int constraint PK_Company primary key constraint FK_Company_Accounts foreign key references Accounts(AccId),
		[CompanyName] varchar(30) unique not null,
		[Designation] varchar(max) not null,
		[Fax] varchar(30) not null
	)
	print N'Create table Company Success'

	create table [Driver]
	(
		[DriverId]  int constraint PK_Driver primary key constraint FK_Driver_Accounts foreign key references Accounts(AccId),
		[Experience] varchar(max) not null,
		[Desciption] varchar(max) not null
	)
	print N'Create table Driver Success'
	
	create table [AdvertiseType]
	(
		[AdvertiseTypeId] int identity(0,1) constraint PK_AdventiseType primary key,
		[AdvertiseName] varchar(50) not null unique
	)
	print N'Create table AdvertiseType Success'
	
	create table [Advertise]
	(
		[AdvertiseId] int constraint PK_Advertise primary key constraint FK_Advertise_Accounts foreign key references Accounts(AccId),
		[CompanyName] varchar(30) not null,
		[Designation] varchar(max) not null,
		[Fax] varchar(20) not null,
		[Description] varchar(max) not null,
		[AdvertiseTypeId] int not null constraint FK_Advertise_AdvertiseType foreign key references AdvertiseType(AdvertiseTypeId)
	)
	print N'Create table Advertise Success'
	
	create table [Order]
	(
		[OrderId] varchar(8) constraint PK_Order primary key,
		[AccId] int not null constraint FK_OrderFrom_Accounts foreign key references Accounts(AccId),
		[OrderStart] datetime not null default getdate(),
		[OrderEnd] datetime not null,
		[CityId] int not null constraint FK_Order_City foreign key references City(CityId),
		[Content] varchar(max) not null
	)
	print N'Create table Order Success'
	
	create table [FeedbackType]
	(
		[FBTypeId] int identity(0,1) constraint PK_FeedbackType primary key ,		
		[FBName] varchar(50) not null unique
	)
	print N'Create table FeedbackType Success'
	
	create table [Feedback]
	(
		[FBId] varchar(8) constraint PK_Feedback primary key,
		[AccountId] int constraint FK_Feedback_Accounts foreign key references Accounts(AccId),	
		[FBTypeId] int not null constraint FK_FeedBack_FeedbackType foreign key references FeedbackType(FBTypeId),
		[FBContent] varchar(max) not null
	)
	print N'Create table Feedback Success'
end try
begin catch
	SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go

create  proc AddNewAccountType
@Name varchar(50)
as
begin try
insert [AccountsType] values(@Name)
print N'Add new AccountType Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc UpdateAccountType
@AccountTypeId int,
@NewName varchar(50)
as
begin try
update [AccountsType] set [TypeName] = @NewName where [TypeId] = @AccountTypeId
print N'Update AccountType Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc DeleteAccountType
@Name varchar(50)
as
begin try
delete [AccountsType] where [TypeName] = @Name
print N'Delete AccountType Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
	exec AddNewAccountType 'Admin'
	exec AddNewAccountType 'User'
	exec AddNewAccountType 'Driver'
	exec AddNewAccountType 'Advertise'
	exec AddNewAccountType 'Free Company'
	exec AddNewAccountType 'Basic Company'
	exec AddNewAccountType 'Premium Company'
go
create  proc AddNewCity
@Name varchar(50)
as
begin try
insert [City] values(@Name)
print N'Add new City Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc UpdateCity
@CityId int,
@NewName varchar(50)
as
begin try
update [City] set [CityName] = @NewName where [CityId] = @CityId
print N'Update City Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc DeleteCity
@Name varchar(50)
as
begin try
delete [City] where [CityName] = @Name
print N'Delete City Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
	exec AddNewCity 'Delhi'
	exec AddNewCity 'Faridabad'
	exec AddNewCity 'Ghaziabad'
	exec AddNewCity 'Guagaon'
	exec AddNewCity 'Noida'
go
create  proc AddNewPaymentRate
@Name varchar(50),
@Price float,
@Date int
as
begin try
insert [PaymentRate] values(@Name,@Price,@Date)
print N'Add new PaymentRate Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc UpdatePaymentRate
@Name varchar(50),
@NewPrice float,
@NewDate int
as
begin try
update [PaymentRate] set [PaymentPrice] = @NewPrice where [PaymentName] = @Name
update [PaymentRate] set [NoDate] = @NewDate where [PaymentName] = @Name
print N'Update PaymentRate Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc DeletePaymentRate
@Id int
as
begin try
delete [PaymentRate] where [PaymentId] = @Id
print N'Delete PaymentRate Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
	exec AddNewPaymentRate 'Free Company',15,30
	exec AddNewPaymentRate 'Free Company',40,90
	exec AddNewPaymentRate 'Premium Company',20,30
	exec AddNewPaymentRate 'Premium Company',55,90
	exec AddNewPaymentRate 'Driver',10,30
	exec AddNewPaymentRate 'Driver',25,90
	exec AddNewPaymentRate 'Advertise',15,30
	exec AddNewPaymentRate 'Advertise',40,90
go
create  proc AddNewFeedbackType
@Name varchar(50)
as
begin try
insert [FeedbackType] values(@Name)
print N'Add new FeedbackType Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc UpdateFeedbackType
@Id int,
@NewName varchar(50)
as
begin try
update [FeedbackType] set [FBName] = @NewName where [FBTypeId] = @Id
print N'Update FeedbackType Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
create  proc DeleteFeedbackType
@Id int
as
begin try
delete [FeedbackType] where [FBTypeId] = @Id
print N'Delete FeedbackType Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
end catch
go
	exec AddNewFeedbackType 'Complaint'
	exec AddNewFeedbackType 'Suggestion'
	exec AddNewFeedbackType 'Compliment'
go
create  proc AddNewUser
@Uid varchar(50),
@Pwd varchar(50),
@Gender bit,
@Img varchar(max),
@Name varchar(50),
@City int,
@Mobile varchar(50),
@Telephone varchar(50),
@Address varchar(max),
@Email varchar(50)
as
begin try
begin tran
insert [Accounts] values(@Uid,@Pwd,@Gender,getdate(),null,null,@Img,1,1)
commit
declare @accid int
set @accid=(select [AccId] from [Accounts] where [Uid]=@Uid)
insert [AccountDetails] values(@accid,@Name,@City,@Mobile,@Telephone,@Address,@Email)
print N'Add new user Success'
end try
begin catch
SELECT ERROR_MESSAGE() AS ErrorMessage
rollback tran
end catch
go
exec AddNewUser 'NguyenVinh','huuvinh',1,'test','Nguyen Huu Vinh',1,'0987992503','0663820234','tay ninh','glory20069@yahoo.com'